﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using HHL_Acc.HHL_DaoAcc.DataBase;
using HHL_Acc.HHL_EntityAcc;
namespace HHL_Acc.HHL_DaoAcc.DataObject
{
    class SmiExportsDao : CBaseDao
    {
        public IList<SmiExports> GetSmiExportDetails(int size,int skip)
        {

            string sql =
                @"Select top " + size + @" ROW_NUMBER() OVER (ORDER BY Smi_Exports.ExpID) as Idx,Smi_Exports.ExpID as ExpID,Smi_Exports.Exp_Number as Exp_Number,Smi_Exports.Issue_Year as Issue_Year,
Smi_Exports.Exp_Date as Exp_Date, sexp.ExpName as ExpMethodName,SUM(mid.Qty) as Qty,Smi_Exports.Login as Login,(em.LASTNAME + em.FIRSTNAME) as ReceiverName,
        Smi_Exports.Notes as Exp_Notes,sta.StationName as StationIDName, Smi_Exports.Status as Status
        from Smi_Exports inner join GEN_LOGINS lo on Smi_Exports.ExpID not in(select top " + skip + @" Smi_Exports.ExpID from Smi_Exports) and Smi_Exports.Receiver = lo.LOGIN and lo.DISABLED =0
inner join GEN_EMPLOYEES em on lo.EMPID = em.EMPID and em.DISABLED = 0    
        inner join Sys_ExpMethods sexp on sexp.ExpMethod = Smi_Exports.ExpMethod and sexp.Disabled = 0
        inner join Smi_Export_Details mid on Smi_Exports.ExpID = mid.ExpID
 inner join Sys_Stations sta on Smi_Exports.StationID = Sta.StationID
        group by Smi_Exports.ExpID,Smi_Exports.Exp_Number,Smi_Exports.Exp_Date,
        Smi_Exports.Issue_Year,sexp.ExpName,em.LASTNAME,em.FIRSTNAME,Smi_Exports.Notes,Smi_Exports.Login,sta.StationName,Smi_Exports.Status
 order by Smi_Exports.Exp_Number";
            DataSet ds = ExcuteSql(sql);
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                return CDb.MapList<SmiExports>(ds.Tables[0]);
            }

            return null;
        }
        //Where exp.ExpID not in(select top 3 exp.ExpID from Smi_Exports) 
        public IList<SmiExports> GetSmiExportDetails1()
        {
            string sql =
                @"Select ROW_NUMBER() OVER (ORDER BY exp.ExpID) as Idx,exp.Exp_Number as Exp_Number,exp.Issue_Year as Issue_Year,
exp.Exp_Date as Exp_Date, sexp.ExpName as ExpMethodName,SUM(mid.Qty) as Qty,exp.Login as Login,(em.LASTNAME + em.FIRSTNAME) as ReceiverName,
        exp.Notes as Exp_Notes,sta.StationName as StationIDName, exp.Status as Status
        from Smi_Exports exp inner join GEN_LOGINS lo on exp.Receiver = lo.LOGIN and lo.DISABLED =0
inner join GEN_EMPLOYEES em on lo.EMPID = em.EMPID and em.DISABLED = 0    
        inner join Sys_ExpMethods sexp on sexp.ExpMethod = exp.ExpMethod and sexp.Disabled = 0
        inner join Smi_Export_Details mid on exp.ExpID = mid.ExpID
 inner join Sys_Stations sta on exp.StationID = Sta.StationID
        group by exp.ExpID,exp.Exp_Number,exp.Exp_Date,
        exp.Issue_Year,sexp.ExpName,em.LASTNAME,em.FIRSTNAME,exp.Notes,exp.Login,sta.StationName,exp.Status";
            DataSet ds = ExcuteSql(sql);
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                return CDb.MapList<SmiExports>(ds.Tables[0]);
            }

            return null;
        }
        public SmiImportDetails GetSmiImportDetail(String importId)
        {
            String sql = "Select * from Smi_Import_Details WHERE importId=@ImportID";
            DataSet ds = ExcuteSql(sql, CommandType.Text, new SqlParameter("@ImportID", importId));
            SmiImportDetails smiImportDetails = CDb.Map<SmiImportDetails>(ds.Tables[0]);
            return smiImportDetails;
        }
        public int GetTotalRecord()
        {
            String sql = "select count(ExpID) from Smi_Exports";
            try
            {
                OpenConnection();
                SqlCommand com = new SqlCommand(sql, Connection);
                int iResult = (int)com.ExecuteScalar();
                CloseConnection();
                return iResult;
            }
            catch (SqlException ass)
            {
                //CommonUtil.WriteToLogFile("Can not execute query: " + pQuery + "\n" + ass.Message);
            }
            finally
            {
                CloseConnection();
            }
           
            return 0;
        }
        public int InsertSmiExports(SmiExports exports)
        {
            const String sql =
                "INSERT Smi_Exports(ExpID,Login,Receiver,ExpMethod,Exp_Date,Exp_Hour,ShiftID,Issue_Year,Exp_Number,Status,LastModified,StationID,Notes) VALUES (@ExpID,@Login,@Receiver,@ExpMethod,@Exp_Date,@Exp_Hour,@ShiftID,@Issue_Year,@Exp_Number,@Status,@LastModified,@StationID,@Notes)";
            SqlParameter[] paramsSql = new SqlParameter[13];
            paramsSql[0] = new SqlParameter("@ExpID", exports.ExpID);
            paramsSql[1] = new SqlParameter("@Login", exports.Login);
            paramsSql[2] = new SqlParameter("@Receiver", exports.Receiver);
            paramsSql[3] = new SqlParameter("@ExpMethod", exports.ExpMethod);
            paramsSql[4] = new SqlParameter("@Exp_Date", exports.Exp_Date);
            paramsSql[5] = new SqlParameter("@Exp_Hour", exports.Exp_Hour);
            paramsSql[6] = new SqlParameter("@ShiftID", exports.ShiftID);
            paramsSql[7] = new SqlParameter("@Issue_Year", exports.Issue_Year);
            paramsSql[8] = new SqlParameter("@Status", exports.Status);
            paramsSql[9] = new SqlParameter("@Exp_Number", exports.Exp_Number);
            paramsSql[10] = new SqlParameter("@LastModified", exports.LastModified);
            paramsSql[11] = new SqlParameter("@StationID", exports.StationID);
            paramsSql[12] = new SqlParameter("@Notes", exports.Exp_Notes);

            return ExcuteNoneQuery(sql, paramsSql);
        }


        public int UpdateSmiExports(SmiExports exports)
        {
            const String sql =
                @"Update Smi_Exports set Login = @Login ,Receiver = @Receiver,ExpMethod = @ExpMethod,Issue_Year = @Issue_Year,
Exp_Number = @Exp_Number,Status = @Status,LastModified = @LastModified,StationID = @StationID,Notes = @Notes WHERE ExpID =  @ExpID";
            SqlParameter[] paramsSql = new SqlParameter[10];
            paramsSql[0] = new SqlParameter("@ExpID", exports.ExpID);
            paramsSql[1] = new SqlParameter("@Login", exports.Login);
            paramsSql[2] = new SqlParameter("@Receiver", exports.Receiver);
            paramsSql[3] = new SqlParameter("@ImpMethod", exports.ExpMethod);
            paramsSql[4] = new SqlParameter("@Issue_Year", exports.Issue_Year);
            paramsSql[5] = new SqlParameter("@Status", exports.Status);
            paramsSql[6] = new SqlParameter("@Exp_Number", exports.Exp_Number);
            paramsSql[7] = new SqlParameter("@LastModified", exports.LastModified);
            paramsSql[8] = new SqlParameter("@StationID", exports.StationID);
            paramsSql[9] = new SqlParameter("@Notes", exports.Exp_Notes);

            return ExcuteNoneQuery(sql, paramsSql);
        }
        public int UpdateSmiExportsStatus(SmiExports exports)
        {
            const String sql =
                @"Update Smi_Exports set Status = @Status WHERE ExpID =  @ExpID";
            SqlParameter[] paramsSql = new SqlParameter[2];
            paramsSql[0] = new SqlParameter("@ExpID", exports.ExpID);
            paramsSql[1] = new SqlParameter("@Status", exports.Status);
            return ExcuteNoneQuery(sql, paramsSql);
        }
    }
}
